Import Sources

The following sections detail the requirements and import settings for each import source.

Delimited file

You can import data from a delimited file. The delimited file must meet the following criteria:

  • Delimiters can be any character. You specify the delimiting character in the import settings.
  • The first row of the file can optionally contain column header names.
  • Numeric values cannot be in scientific notation or formatted with extraneous characters such as currency signs or parentheses.

The Source tab of the Import Wizard uses the following settings when importing from a delimited file.

Item Description
Source

Select Delimited File to import data from a delimited text file, such as CSV or TXT files. The first row can optionally contain header names.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option (see the File Location setting), then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

File Location

The name and location of the source file. Select one of the following options:

  • Always use this file: Select this option if the file name and location is always known. In the File box, type the path and file name. You can click the folder icon to navigate to the file.

    The file path must be a UNC path (For example, \\servername\foldername\filename). If you type a mapped drive, it automatically converts to a UNC path.

    The location of this file must be accessible to the Axiom Application Server. When you specify a file, Axiom Software validates whether the application server can access the file, and displays an error if not. For assistance in resolving this error, contact Kaufman Hall Support.

  • Prompt for file during execution: Select this option to allow the user to specify the file when running the import. If desired, in the Folder box, you can specify a folder location. When the user is prompted to select a file, it opens to this folder by default. The user can still browse to a different location.

    In this case, a copy of the specified file uploads to the application server for processing. After the import is complete, the temporary copy of the file is deleted.

    NOTE: Files greater than 100 MB cannot be uploaded using the prompt option. While it is possible to increase this limit, it is not recommended. Instead, you should use Always use this file if you need to import a file larger than 100 MB. Please contact Kaufman Hall Support if you need assistance with a large file.

You can use variables to specify the file name or location.

First row has column names

Select this option if the first row of the file contains column names. If the first row of the file contains data, leave this option unselected.
Import file has multi-line values

Select this option if the import file has data where a field value splits across rows (within the text qualifier).

If this option is selected, then the split value reads as a single import value.

Delimiter

In the box to the right of the option, type the delimiting character used in the source file. For example, if the delimiter is a comma, type a comma in the box.

NOTE: We prefer that the file is pipe-delimited (uses the | character).

If the delimiting character is a space or a tab, place your cursor in the box, and press the space bar or the tab key. The character is indicated in parentheses to the right of the box (since the character is not visible in this case).

Text Qualifier

By default, the text qualifier is double quotation marks ("). If desired, you can enter a different character as the text qualifier, or you can clear the field if you do not want to use a text qualifier.

The text qualifier is used when values in the source file may contain the delimiting character. For example, if the delimiting character is a comma, but the source file contains values such as full names that also contain a comma (For example, "Doe, Jane"). In this case, the comma within the quotation marks is considered part of the field value instead of starting a new field.

Excel file

You can import data from an Excel file. The Excel file must meet the following criteria:

  • The file format must be XLS or XLSX. XLSM files cannot be imported.

  • The first row can optionally contain header names.

  • Each column in the Excel file translates to a column in the destination table. Each row in the file translates to a data record in the table. Blank columns and rows are ignored.

  • The data in the spreadsheet must match the designated data type for the destination column. For example, if numeric values in the spreadsheet are prefixed with a quotation mark, then Excel considers those values text instead of numbers. This will cause an error if attempting to import these text values into a Numeric, Decimal, or Integer column.

The Source tab of the Import Wizard uses the following settings when importing from an Excel file.

Item Description
Source Select Excel File to import data from an Excel file.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option (see the File Location setting), then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Sheet name

The sheet in the Excel file to import. Leave this blank to use the first sheet in the file. You can only import one sheet.

You can use variables to specify the sheet name.

File Location

The name and location of the source file. Select one of the following options:

  • Always use this file: Select this option if the file name and location is always known. In the File box, type the path and file name. You can click the folder icon to navigate to the file.

    The file path must be a UNC path (For example, \\servername\foldername\filename). If you type a mapped drive, it automatically converts to a UNC path.

    The location of this file must be accessible to the Axiom Application Server. When you specify a file, Axiom Software validates whether the application server can access the file, and will display an error if not.

  • Prompt for file during execution: Select this option to allow the user to specify the file when running the import. If desired, in the Folder box, you can specify a folder location. When the user is prompted to select a file, it opens to this folder by default. The user can still browse to a different location.

    In this case, a copy of the specified file uploads to the application server for processing. After the import is complete, the temporary copy of the file is deleted.

    NOTE: Files greater than 100 MB cannot be uploaded using the "prompt" option. While it is possible to increase this limit, it is not recommended. Instead, you should use Always use this file if you need to import a file larger than 100 MB. Please contact Kaufman Hall Support if you need assistance with a large file.

You can use variables to specify the file name or location.

First row has column names

Select this option if the first row of the file contains column names. If the first row of the file contains data, leave this option unselected.

SQL Server

The Source tab of the Import Wizard uses the following settings when importing data from a SQL Server database.

Item Description
Source Select SQL Server to read data directly from a SQL Server database.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option, then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection

Complete the following connection information for the import source. After you complete the connection settings, click the Test connection button to test the connection. The Status updates to show a success message or an error message.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Item Description

Server

The name of the SQL Server.

Database

The name of the database.

User

The user name to use to connect to the specified server and database. The user credentials must be for a SQL Server account; you cannot use network domain credentials.

Password

The password to use to connect to the specified server and database.

NOTE: The password must be re-entered whenever any of the other connection properties are changed.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

Connection

Complete the following connection information for the import source. After you complete the connection settings, click the Test connection button to test the connection. The Status updates to show a success message or an error message.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Item Description

Server

The name of the SQL Server.

Database

The name of the database.

User

The user name to use to connect to the specified server and database. The user credentials must be for a SQL Server account; you cannot use network domain credentials.

Password

The password to use to connect to the specified server and database.

NOTE: The password must be re-entered whenever any of the other connection properties are changed.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

Oracle

The Source tab of the Import Wizard uses the following settings when importing data from an Oracle database.

Field Description
Source

Select Oracle to read data directly from an Oracle database.

NOTE: Your organization must install the Oracle Data Access Connection software (ODAC) on the Axiom system application server to use this import option. To import directly from an Oracle database without installing this software on the application server, you can use the OLEDB import source instead.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option, then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection

Complete the following connection information for the import source.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Field Description

Server

The connection parameters for the Oracle server. You can obtain this information from the Oracle TNS Names entry. See the following section for more information.
User

The user name to use to connect to the database.
Password

The password to use to connect to the database.

NOTE: The password must be re-entered whenever any of the other connection properties are changed.

Obtaining the Oracle connection parameters from a TNS Names entry

The following is an example of a typical TNS Names entry for Oracle:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID)))

Axiom requires this information in the following format:

MyHOSTNAME:MyPORT/MyOracleServiceID

Where:

  • MyHostName is the name of the Oracle server machine.

  • MyPort is the port number that the server is listening on, typically 1521.

  • MyOracleServiceID is the name of the Oracle service running on the host machine.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

Connection

Complete the following connection information for the import source.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Field Description

Server

The connection parameters for the Oracle server. You can obtain this information from the Oracle TNS Names entry. See the following section for more information.
User

The user name to use to connect to the database.
Password

The password to use to connect to the database.

NOTE: The password must be re-entered whenever any of the other connection properties are changed.

Obtaining the Oracle connection parameters from a TNS Names entry

The following is an example of a typical TNS Names entry for Oracle:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID)))

Axiom requires this information in the following format:

MyHOSTNAME:MyPORT/MyOracleServiceID

Where:

  • MyHostName is the name of the Oracle server machine.

  • MyPort is the port number that the server is listening on, typically 1521.

  • MyOracleServiceID is the name of the Oracle service running on the host machine.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

Obtaining the Oracle connection parameters from a TNS Names entry

The following is an example of a typical TNS Names entry for Oracle:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID)))

Axiom requires this information in the following format:

MyHOSTNAME:MyPORT/MyOracleServiceID

Where:

  • MyHostName is the name of the Oracle server machine.

  • MyPort is the port number that the server is listening on, typically 1521.

  • MyOracleServiceID is the name of the Oracle service running on the host machine.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

OLEDB

Use the OLEDB option on the Import Wizard's Source tab to connect to any database or file that supports OLEDB, with the following exceptions:

  • If the database is a SQL Server or Oracle database, you can use the database-specific options instead. However, you can use OLEDB to connect to these database types if desired.
  • If the file is an Excel file, you cannot use OLEDB. Use the Excel File option instead.

The Source tab of the Import Wizard uses the following settings when importing data using an OLEDB connection.

Item Description
Source

Select OLEDB to read data directly from a database or a file using an OLEDB connection.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option, then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection

The connection string identifies the name and location of the database or file to connect to, including any necessary validation information. In the Connection string box, type the connection string to the source database or file. You can use any valid SQL connection string. The connection string cannot contain spaces.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

After you complete the connection settings, click the Test connection button to test the connection. The Status updates to show a success message or an error message.

NOTE: If the connection string contains a password, that password must be re-entered whenever any of the other connection properties are changed.

A good resource for connection strings is http://www.connectionstrings.com/. The following table lists examples of common connection strings:

Source Sample string

CSV

Server=.\SQLExpress;Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};UID=test;PWD=test!123;Database=AxiomFinancial

SQL Server 2005, trusted connection

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Oracle with TNS

Data Source=TORCL;User Id=myUsername;Password=myPassword;

SQLOLEDB (standard)

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQLOLEDB (trusted)

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

SQLOLEDB (server instance)

Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

AS400

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

Connection

The connection string identifies the name and location of the database or file to connect to, including any necessary validation information. In the Connection string box, type the connection string to the source database or file. You can use any valid SQL connection string. The connection string cannot contain spaces.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

After you complete the connection settings, click the Test connection button to test the connection. The Status updates to show a success message or an error message.

NOTE: If the connection string contains a password, that password must be re-entered whenever any of the other connection properties are changed.

A good resource for connection strings is http://www.connectionstrings.com/. The following table lists examples of common connection strings:

Source Sample string

CSV

Server=.\SQLExpress;Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};UID=test;PWD=test!123;Database=AxiomFinancial

SQL Server 2005, trusted connection

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Oracle with TNS

Data Source=TORCL;User Id=myUsername;Password=myPassword;

SQLOLEDB (standard)

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQLOLEDB (trusted)

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

SQLOLEDB (server instance)

Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

AS400

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, type any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement.

You can use variables in the SELECT statement.

ODBC

Use the ODBC option on the Import Wizard's Source tab to connect to any database that supports Open Database Connectivity. Generally speaking, you should only use this option if no other option is available to connect to your desired database. If you are connecting to a SQL Server or Oracle database, use the database-specific options instead.

The Source tab of the Import Wizard uses the following settings when importing data using an ODBC connection.

Item Description
Import source Select ODBC to read data directly from a database using an ODBC connection.
Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option, then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select from any remote data connection set up in Scheduler. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection

The connection string identifies the name and location of the database to connect to, including any necessary authentication credentials. The connection string requirements and syntax vary depending on the source database you are attempting to connect to. Consult the documentation from your database vendor to determine an appropriate ODBC connection string for this purpose.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

After you complete the connection settings, click the Test connection button to test the connection. The Status updates to show a success message or an error message.

NOTE: If the connection string contains a password, that password must be re-entered whenever any of the other connection properties are changed.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, enter any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

ODBC driver

Use of ODBC requires your organization to install an ODBC driver on the following servers:

  • For on-premise systems, the driver must be installed on the Axiom Software Application Server.
  • For cloud service systems, the driver must be installed on the local server that is hosting the Axiom Software Cloud Integration Service.

The ODBC driver is specific to your source database. To use ODBC with a particular database, the database vendor must provide or recommend an ODBC driver to use with that database.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to import to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, enter any valid SQL statement to define the data query. Click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements and several tools to check the statement.

You can use variables in the SELECT statement.

ODBC driver

Use of ODBC requires your organization to install an ODBC driver on the following servers:

  • For on-premise systems, the driver must be installed on the Axiom Software Application Server.
  • For cloud service systems, the driver must be installed on the local server that is hosting the Axiom Software Cloud Integration Service.

The ODBC driver is specific to your source database. To use ODBC with a particular database, the database vendor must provide or recommend an ODBC driver to use with that database.

Intacct

NOTE: This import source is only available if you have licensed Intacct integration.

The Source tab of the Import Wizard uses the following settings when importing data from Intacct.

Item Description
Source Select Intacct to read data directly from Intacct.

Connection

Complete the following connection information for the import source.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Item Description

User ID

The user name to use to connect to Intacct.

Company ID

The company ID to use to connect to Intacct.

Password

The password to use to connect to Intacct.

Table Name

The Intacct table from which to read data.

Filter

A filter to limit the data to be read from the table.

The following SQL operators are supported in the filter: <, >, >=, <=, =, LIKE, NOT LIKE, IN, NOT IN. When doing NULL comparisons, use IS NOT NULL or IS NULL. Compound filters using AND and OR are supported, but joins are not supported. If the value you are filtering on contains an apostrophe, add a backslash before it to escape the apostrophe (for example: contactname = 'Erik\'s Deli').

Column Names

A comma-delimited list of columns to be read from the table. Leave this blank to return all columns.

Creating the SQL SELECT statement

If the import source is SQL Server, Oracle, OLEDB, or one of the current Axiom database options, then you must define a SQL SELECT statement to query the source database, resulting in the set of data to be imported to the temptable. You can use the Edit SQL dialog to create and test the SELECT statement.

To open the dialog:

  • On the Source tab of the Import Wizard, click the browse button (...) to the right of the SQL Select Statement box.

The Edit SQL dialog provides a text editor for the statement, and also several tools to help create and test the statement.

Creating the statement

You can type the statement into the text editor, or copy and paste from another source.

You can use the Choose Table tool to automatically generate a SQL statement that selects all columns in a specified table. You can then edit the statement to meet the specific data needs. To do this:

  1. Click the Choose source table to create SQL button.

  2. In the Choose Table dialog, select the table for which to generate the SQL statement, and then click OK.

    The Choose Table dialog lists all tables in the SQL Server database specified on the Source tab, including views.

The generated SELECT statement is placed in the text editor. Any existing text in the editor is overwritten.

Testing the statement

NOTE: If the SQL statement uses variables, then these validation features are not available. Validation features are also not available if the source is OLEDB.

To validate the syntax of the SQL statement, click the Check SQL syntax button. The Axiom system sends the statement to your database server to see if the statement can be parsed, resulting in either a success message or an error message.

To view a set of sample records, click the View data button. The Axiom system queries the database and returns the first 100 rows in the View Data dialog. You can review this data to help determine if the SELECT statement is returning the desired set of data.

Within the View Data dialog, you can sort, filter, and group the sample data using standard Axiom grid functionality. If desired, you can click the Export data to .CSV file button to export the data to a CSV file.

When you are finished reviewing the data, click Close to return to the Edit SQL dialog.

To view a set of sample records, click the View data button. The Axiom system queries the database and returns the first 100 rows in the View Data dialog. You can review this data to help determine if the SELECT statement is returning the desired set of data.

Within the View Data dialog, you can sort, filter, and group the sample data using standard Axiom grid functionality. If desired, you can click the Export data to .CSV file button to export the data to a CSV file.

When you are finished reviewing the data, click Close to return to the Edit SQL dialog.

Import source file considerations

If the import source is a delimited file or an Excel file, then you must consider how the file is specified. There are two options: Prompt for file location and Always use this file.

File Source File Permissions Data Flow Ramifications / Limitations
Prompt for file location File must be accessible by the user’s file system permissions.
  • A copy of the file is streamed from the customer machine to the application server.
  • Import from the application server to the database server.
  • The copy of the file on the application server is deleted after the import is complete.
  • Slower performance (file is copied multiple times)
  • Only available when running the import interactively
  • Limited to files less than 100MB
Always use this file
  • File must be accessible by the application server's permissions
  • File path must be a UNC path, not a mapped drive (meaning \\servername\foldername, not I:\foldername)
 The file is streamed from the file source to the database server for import.
  • Faster performance
  • No practical limit to the file size
  • Can be used in Scheduler